﻿Imports ĐŘječnik.ImportExport.Simple, System.ComponentModel
Imports System.Data.SqlServerCe
Namespace ImportExport
    ''' <summary>Realizes import from "simple" file</summary>
    Public Class frmSimple
        ''' <summary>Number of current step in process</summary>
        Private CurrentStep As Byte = 0
        ''' <summary>In-memory representation of loaded dictionary</summary>
        Private LoadedDictionary As ImportExport.Simple.Dictionary

        Private Sub frmSimple_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            Select Case CurrentStep
                Case 255 'Allow close
                Case 0 'Do not allow close
                    e.Cancel = True
                Case 1 'Close is handled by cancel-handling routine
                    cmdStep1Cancel_Click(sender, e)
                    e.Cancel = True
                Case 2, 4 'Allow Close
                Case 3 'Close is handled by cancel-handling routine
                    cmdStep3Cancel_Click(sender, e)
                    e.Cancel = True
            End Select
        End Sub
#Region "Load XML"
        Private Sub frmSimple_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Me.Show()
            If ofdSimple.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim xml As New Xml.XmlDocument
                Try
                    xml.Load(ofdSimple.FileName)
                Catch ex As Exception
                    MsgBox(My.Resources.ErrorWhileLoadingXMLFile & vbCrLf & ex.Message, MsgBoxStyle.Critical, My.Resources.Error_)
                    Me.Close()
                    Me.DialogResult = Windows.Forms.DialogResult.Abort
                    Exit Sub
                End Try
                lblStep1FileName.Text = ofdSimple.FileName
                'Goto Step 1
                CurrentStep = 1
                bgwLoad.RunWorkerAsync(xml)
            Else
                Me.DialogResult = Windows.Forms.DialogResult.Cancel
                CurrentStep = 255 'Allow close
                Me.Close()
            End If
        End Sub

        Private Sub bgwLoad_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgwLoad.DoWork
            Try
                LoadedDictionary = New ImportExport.Simple.Dictionary(e.Argument, sender)
            Catch ex As Exception
                e.Result = ex
            End Try
        End Sub

        Private Sub bgwLoad_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgwLoad.ProgressChanged
            pgbStep1.Value = e.ProgressPercentage
            If e.UserState IsNot Nothing AndAlso TypeOf e.UserState Is String Then
                lblStep1Info.Text = e.UserState
            End If
        End Sub

        Private Sub bgwLoad_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwLoad.RunWorkerCompleted
            If e.Cancelled OrElse (e.Result IsNot Nothing AndAlso TypeOf e.Result Is OperationCanceledException) Then
                Me.DialogResult = Windows.Forms.DialogResult.Cancel
                CurrentStep = 255
                Me.Close()
                Exit Sub
            ElseIf e.Result IsNot Nothing AndAlso TypeOf e.Result Is Exception Then
                MsgBox(My.Resources.ErrorWhileLoadingXMLDictionary & vbCrLf & DirectCast(e.Result, Exception).Message, MsgBoxStyle.Critical, My.Resources.Error_)
                Me.DialogResult = Windows.Forms.DialogResult.Abort
                Me.Close()
                Exit Sub
            End If
            LoadedDictionary.Path = ofdSimple.FileName
            'Goto step 2
            CurrentStep = 2
            EnabledStep(1) = False
            EnabledStep(2) = True
            lblStep1Status.Text = My.Resources.MarkDone
            lblStep1Status.ForeColor = Color.Green
            lblStep2Status.Text = My.Resources.MarkActual

            lblDictionaryName1.Text = LoadedDictionary.Name1
            lblDictionaryName2.Text = LoadedDictionary.Name2
            lblDictionaryNameE.Text = LoadedDictionary.NameE
            lblLangauge1_1.Text = LoadedDictionary.Native1
            lblLangauge1_2.Text = LoadedDictionary.Other1
            lblLanguage2_1.Text = LoadedDictionary.Other2
            lblLangauge2_2.Text = LoadedDictionary.Native2
            lblAuthor.Text = LoadedDictionary.Author.Nick
            If LoadedDictionary.Original IsNot Nothing Then lblOriginalAuthor.Text = LoadedDictionary.Original.Nick
            lblTrnsPairs.Text = LoadedDictionary.Pairs.Count
            chkPrimary.Checked = LoadedDictionary.Primary IsNot Nothing
        End Sub
#End Region
        ''' <summary>Enables or disables controls for one step</summary>
        ''' <param name="Step">Number of step</param>
        Private WriteOnly Property EnabledStep(ByVal [Step] As Byte) As Boolean
            Set(ByVal value As Boolean)
                Select Case [Step]
                    Case 1
                        fraStep1.Enabled = value
                        'lblStep1.Enabled = value
                        'lblStep1Status.Enabled = value
                    Case 2
                        fraStep2.Enabled = value
                        'lblStep2.Enabled = value
                        'lblStep2Status.Enabled = value
                    Case 3
                        fraStep3.Enabled = value
                        'lblStep3.Enabled = value
                        'lblStep3Status.Enabled = value
                    Case 4
                        fraStep4.Enabled = value
                        'lblStep4.Enabled = value
                        'lblStep4Status.Enabled = value
                End Select
            End Set
        End Property

        Private Sub cmdStep1Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStep1Cancel.Click
            bgwLoad.CancelAsync()
        End Sub

        Private Sub cmdStep2Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStep2Cancel.Click
            Me.DialogResult = Windows.Forms.DialogResult.Cancel
            CurrentStep = 255
            Me.Close()
            Exit Sub
        End Sub

        Private Sub cmdStep2Continue_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStep2Continue.Click
            'GoTo Step 3
            lblStep2Status.ForeColor = Color.Green
            lblStep3Status.Text = My.Resources.MarkActual
            lblStep2Status.Text = My.Resources.MarkDone
            EnabledStep(2) = False
            EnabledStep(3) = True
            CurrentStep = 3
            bgwImport.RunWorkerAsync(LoadedDictionary)
        End Sub
        ''' <summary>Results of <see cref="ImportDictionary"/></summary>
        Private Enum Results
            ''' <summary>Import OK</summary>
            OK
            ''' <summary>Import aborted due to error</summary>
            Aborted
            ''' <summary>Import canceled by user</summary>
            Canceled
        End Enum
#Region "Save to DB"
        ''' <summary>Shows <see cref="frmDictionaryExists"/> as modal dialog in correct thread of creation of <see cref="frmSimple"/></summary>
        ''' <param name="ConflictWith">List of dictionaries in name conflict with current dictionary</param>
        ''' <returns>Instance of <see cref="frmDictionaryExists"/> to obtain dialog result and names of dictionary from</returns>
        Private Function DicConfilct(ByVal ConflictWith As ĐŘječnikData.DictionaryDataTable) As frmDictionaryExists
            Dim frmResolve As New frmDictionaryExists(ConflictWith)
            frmResolve.ShowDialog(Me)
            Return frmResolve
        End Function

        ''' <summary>Delegate to create handle of the <see cref="DicConfilct"/> function</summary>
        ''' <param name="ConflictWith">List of dictionaries in name conflict with current dictionary</param>
        ''' <returns>Instance of <see cref="frmDictionaryExists"/> to obtain dialog result and names of dictionary from</returns>
        Private Delegate Function dDicConflict(ByVal ConflictWith As ĐŘječnikData.DictionaryDataTable) As frmDictionaryExists

        ''' <summary>Invokes the <see cref="ShowMessageBox"/> function on thread of creation of this instance</summary>
        ''' <param name="Prompt">Required. <see cref="String"/> expression displayed as the message in the dialog box. The maximum length of <paramref name="Prompt"/> is approximately 1024 characters, depending on the width of the characters used. If Prompt consists of more than one line, you can separate the lines using a carriage return character (<see cref="Chr"/>(13)), a line feed character (<see cref="Chr"/>(10)), or a carriage return/linefeed character combination (<see cref="Chr"/>(13) &amp; <see cref="Chr"/>(10)) between each line.</param>
        ''' <param name="Title">Optional. <see cref="String"/> expression displayed in the title bar of the dialog box. If you omit <paramref Name="Title"/>, the application name is placed in the title bar.</param>
        ''' <param name="Buttons">Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If you omit <paramref name="Buttons"/>, the default value is zero.</param>
        ''' <returns>Constant OK, Value 1. Constant Cancel, Value 2. Constant Abort, Value 3. Constant Retry, Value 4. Constant Ignore, Value 5. Constant Yes, Value 6. Constant No, Value 7.</returns>
        Private Function ModalMsgBox(ByVal Prompt As String, Optional ByVal Buttons As MsgBoxStyle = MsgBoxStyle.OkOnly, Optional ByVal Title As String = Nothing) As MsgBoxResult
            Return Me.Invoke(New dShowMessageBox(AddressOf ShowMessageBox), Prompt, Buttons, Title)
        End Function
        ''' <remarks>Delegate for invoking the <see cref="ShowMessageBox"/> function</remarks>
        ''' <param name="Prompt">Required. <see cref="String"/> expression displayed as the message in the dialog box. The maximum length of <paramref name="Prompt"/> is approximately 1024 characters, depending on the width of the characters used. If Prompt consists of more than one line, you can separate the lines using a carriage return character (<see cref="Chr"/>(13)), a line feed character (<see cref="Chr"/>(10)), or a carriage return/linefeed character combination (<see cref="Chr"/>(13) &amp; <see cref="Chr"/>(10)) between each line.</param>
        ''' <param name="Title">Optional. <see cref="String"/> expression displayed in the title bar of the dialog box. If you omit <paramref Name="Title"/>, the application name is placed in the title bar.</param>
        ''' <param name="Buttons">Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If you omit <paramref name="Buttons"/>, the default value is zero.</param>
        ''' <returns>Constant OK, Value 1. Constant Cancel, Value 2. Constant Abort, Value 3. Constant Retry, Value 4. Constant Ignore, Value 5. Constant Yes, Value 6. Constant No, Value 7.</returns>
        Private Delegate Function dShowMessageBox(ByVal Prompt As String, ByVal Buttons As MsgBoxStyle, ByVal Title As String) As MsgBoxResult
        ''' <summary>Shows a message boxu using <see cref="MsgBox"/></summary>
        ''' <param name="Prompt">Required. <see cref="String"/> expression displayed as the message in the dialog box. The maximum length of <paramref name="Prompt"/> is approximately 1024 characters, depending on the width of the characters used. If Prompt consists of more than one line, you can separate the lines using a carriage return character (<see cref="Chr"/>(13)), a line feed character (<see cref="Chr"/>(10)), or a carriage return/linefeed character combination (<see cref="Chr"/>(13) &amp; <see cref="Chr"/>(10)) between each line.</param>
        ''' <param name="Title">Optional. <see cref="String"/> expression displayed in the title bar of the dialog box. If you omit <paramref Name="Title"/>, the application name is placed in the title bar.</param>
        ''' <param name="Buttons">Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If you omit <paramref name="Buttons"/>, the default value is zero.</param>
        ''' <returns>Constant OK, Value 1. Constant Cancel, Value 2. Constant Abort, Value 3. Constant Retry, Value 4. Constant Ignore, Value 5. Constant Yes, Value 6. Constant No, Value 7.</returns>
        Private Function ShowMessageBox(ByVal Prompt As String, Optional ByVal Buttons As MsgBoxStyle = MsgBoxStyle.OkOnly, Optional ByVal Title As String = Nothing) As MsgBoxResult
            Return MsgBox(Prompt, Buttons, Title)
        End Function


        ''' <summary>Imports dictionary to database</summary>
        Private Function ImportDictionary(ByVal Dic As Simple.Dictionary, ByVal WK As BackgroundWorker) As Results
            Dim Conn As New SqlCeConnection(ConnectionString)

            Conn.Open()
            Dim Commit As Boolean = False
            Dim tx As SqlCeTransaction = Conn.BeginTransaction()
            Try
                'Verify that such dictionary doesn't exist
                WK.ReportProgress(0, My.Resources.CheckingExistingDictionaries)

                Dim taDics As New ĐŘječnikDataTableAdapters.taDictionary
                Dim Found As ĐŘječnikData.DictionaryDataTable = taDics.GetDictionaryByEitherName(Dic.Name1, Dic.Name2, Dic.NameE)
                If Found.Rows.Count > 0 Then

                    Dim frmResolve As frmDictionaryExists = Me.Invoke(New dDicConflict(AddressOf DicConfilct), Found)
                    'frmResolve.ShowDialog()
                    If frmResolve.DialogResult = Windows.Forms.DialogResult.OK Then
                        Dic.NameE = frmResolve.LangE
                        Dic.Name1 = frmResolve.Lang1
                        Dic.Name2 = frmResolve.Lang2
                    Else
                        Return Results.Canceled
                    End If
                End If
                'Verify that there is no conflict between primary dictionaries
                If Dic.Primary IsNot Nothing Then
                    Found = taDics.GetDictionaryByLngComb(Dic.Lang1Code, Dic.Lang2Code)
                    If Found.Rows.Count > 0 Then
                        For Each CurrDic As ĐŘječnikData.DictionaryRow In Found
                            If CurrDic.Primary Then
                                Select Case ModalMsgBox(My.Resources.impPrimaryDictionaryConfilct, MsgBoxStyle.YesNoCancel Or MsgBoxStyle.Question, My.Resources.PrimaryDictionaryConflict)
                                    Case MsgBoxResult.Yes
                                    Case MsgBoxResult.No
                                        Dic.Primary = Nothing
                                    Case Else
                                        Return Results.Canceled
                                End Select
                                Exit For
                            End If
                        Next CurrDic
                    End If
                End If
                WK.ReportProgress(0, My.Resources.CreatingDictionary)
                'Create dictionary
                Dim cmdNewDic As New SqlCeCommand( _
                        "INSERT INTO Dictionary " & _
                        "([Primary], English1, English2, Lang1Code, Lang2Code, Native1, Native2, Other1, Other2, Name1, Name2, NameE, Version, ICO, [Date], PriICO1, " & _
                        "PriICO2, AuthNick, AuthFirstN, AuthLastN, AuthEMail, AuthWWW, AuthNote, OrigNick, OrigFirstN, OrigLastN, OrigEMail, OrigWWW, OrigSource, " & _
                        "OrigNote) " & _
                        "VALUES     (@Primary, @English1, @English2, @Lang1Code, @Lang2Code, @Native1, @Native2, @Other1, @Other2, @Name1, @Name2, @NameE, @Version, " & _
                        "@ICO, @Date, @PriICO1, @PriICO2, @AuthNick, @AuthFirstN, @AuthLastN, @AuthEMail, @AuthWWW, @AuthNote, @OrigNick, @OrigFirstN, " & _
                        "@OrigLastN, @OrigEMail, @OrigWWW, @OrigSource, @OrigNote)", _
                        Conn, tx)
                Dim cmdIdentity As New SqlCeCommand("SELECT @@IDENTITY", Conn, tx)
                With cmdNewDic.Parameters
                    .Add("@Primary", Dic.Primary IsNot Nothing)
                    .Add("@English1", Dic.English1)
                    .Add("@English2", Dic.English2)
                    .Add("@Lang1Code", Dic.Lang1Code)
                    .Add("@Lang2Code", Dic.Lang2Code)
                    .Add("@Native1", Dic.Native1)
                    .Add("@Native2", Dic.Native2)
                    .Add("@Other1", Dic.Other1)
                    .Add("@Other2", Dic.Other2)
                    .Add("@Name1", Dic.Name1)
                    .Add("@Name2", Dic.Name2)
                    .Add("@NameE", Dic.NameE)
                    .Add("@Version", Dic.Version)
                    .Add("@ICO", Dic.ICO)
                    .Add("@Date", Dic.Date)
                    If Dic.Primary IsNot Nothing Then
                        .Add("@PriICO1", Dic.Primary.ICO1)
                        .Add("@PriICO2", Dic.Primary.ICO2)
                    Else
                        .Add("@PriICO1", DBNull.Value).IsNullable = True
                        .Add("@PriICO2", DBNull.Value).IsNullable = True
                    End If
                    .Add("@AuthNick", Dic.Author.Nick)
                    .Add("@AuthFirstN", Dic.Author.FirstName)
                    .Add("@AuthLastN", Dic.Author.LastName)
                    .Add("@AuthEMail", Dic.Author.EMail)
                    .Add("@AuthWWW", Dic.Author.WWW).IsNullable = True
                    .Add("@AuthNote", Dic.Author.Note).IsNullable = True
                    If Dic.Original IsNot Nothing Then
                        .Add("@OrigNick", Dic.Original.Nick)
                        .Add("@OrigFirstN", Dic.Original.FirstName)
                        .Add("@OrigLastN", Dic.Original.LastName)
                        .Add("@OrigEMail", Dic.Original.EMail)
                        .Add("@OrigWWW", Dic.Original.WWW).IsNullable = True
                        .Add("@OrigSource", Dic.Original.Source)
                        .Add("@OrigNote", Dic.Original.Note).IsNullable = True
                    Else
                        .Add("@OrigNick", DBNull.Value).IsNullable = True
                        .Add("@OrigFirstN", DBNull.Value).IsNullable = True
                        .Add("@OrigLastN", DBNull.Value).IsNullable = True
                        .Add("@OrigEMail", DBNull.Value).IsNullable = True
                        .Add("@OrigWWW", DBNull.Value).IsNullable = True
                        .Add("@OrigSource", DBNull.Value).IsNullable = True
                        .Add("@OrigNote", DBNull.Value).IsNullable = True
                    End If
                End With
                cmdNewDic.ExecuteNonQuery()
                Dim DicID As Integer = cmdIdentity.ExecuteScalar()
                'Import translation pairs
                'Ititialization
                WK.ReportProgress(0, My.Resources.ImportingTranslationPairs)
                Dim i As Integer = 0
                Dim cmdSrchW1 As New SqlCeCommand(String.Format("SELECT ID From Word WHERE Dic={0} AND Lang=1 AND Native = @Word", DicID), Conn, tx)
                Dim cmdSrchW2 As New SqlCeCommand(String.Format("SELECT ID From Word WHERE Dic={0} AND Lang=2 AND Native = @Word", DicID), Conn, tx)
                cmdSrchW1.Parameters.Add("@Word", SqlDbType.NVarChar)
                cmdSrchW2.Parameters.Add("@Word", SqlDbType.NVarChar)
                Dim cmdAddW1 As New SqlCeCommand(String.Format("INSERT INTO Word (Dic,Lang,Native,Latin,Type,Note) VALUES ({0},1,@Native,@Latin,@Type,@Note)", DicID), Conn, tx)
                Dim cmdAddW2 As New SqlCeCommand(String.Format("INSERT INTO Word (Dic,Lang,Native,Latin,Type,Note) VALUES ({0},2,@Native,@Latin,@Type,@Note)", DicID), Conn, tx)
                With cmdAddW1.Parameters
                    .Add("@Native", SqlDbType.NVarChar)
                    .Add("@Latin", SqlDbType.NVarChar).IsNullable = True
                    .Add("@Type", SqlDbType.SmallInt).IsNullable = True
                    .Add("@Note", SqlDbType.NVarChar).IsNullable = True
                End With
                With cmdAddW2.Parameters
                    .Add("@Native", SqlDbType.NVarChar)
                    .Add("@Latin", SqlDbType.NVarChar).IsNullable = True
                    .Add("@Type", SqlDbType.SmallInt).IsNullable = True
                    .Add("@Note", SqlDbType.NVarChar).IsNullable = True
                End With
                Dim cmdAddTrnsl As New SqlCeCommand("INSERT INTO Translation (W1,W2) VALUES (@W1,@W2)", Conn, tx)
                cmdAddTrnsl.Parameters.Add("@W1", SqlDbType.BigInt)
                cmdAddTrnsl.Parameters.Add("@W2", SqlDbType.BigInt)
                Dim cmdTrnslExists As New SqlCeCommand("SELECT COUNT(*) FROM Translation WHERE W1 = @W1 AND W2 = @W2", Conn, tx)
                cmdTrnslExists.Parameters.Add("@W1", SqlDbType.BigInt)
                cmdTrnslExists.Parameters.Add("@W2", SqlDbType.BigInt)
                'Import loop
                For Each Pair As Simple.Dictionary.TranslationPair In Dic.Pairs : i += 1
                    If WK.CancellationPending Then Return Results.Canceled
                    Dim Words1 As New List(Of Long)
                    Dim Words2 As New List(Of Long)
                    For Each W1 As Simple.Dictionary.Word In Pair.Words1
                        'Search for word 1
                        cmdSrchW1.Parameters(0).Value = W1.Native
                        Dim W1i As Object = cmdSrchW1.ExecuteScalar
                        If W1i Is Nothing OrElse TypeOf W1i Is DBNull Then
                            'If not found add it
                            With cmdAddW1.Parameters
                                .Item("@Native").Value = W1.Native
                                .Item("@Latin").Value = If(W1.Latin = "", CObj(DBNull.Value), W1.Latin)
                                If W1.Type <> enmType.NA Then
                                    .Item("@Type").Value = W1.Type
                                Else
                                    .Item("@Type").Value = DBNull.Value
                                End If
                                .Item("@Note").Value = If(W1.Note = "", CObj(DBNull.Value), W1.Note)
                            End With
                            cmdAddW1.ExecuteNonQuery()
                            Words1.Add(cmdIdentity.ExecuteScalar)
                        Else
                            Words1.Add(W1i)
                        End If
                        W1.ID = Words1(Words1.Count - 1)
                    Next W1
                    For Each W2 As Simple.Dictionary.Word In Pair.Words2
                        'Search for word 2
                        cmdSrchW2.Parameters(0).Value = W2.Native
                        Dim W2i As Object = cmdSrchW2.ExecuteScalar
                        If W2i Is Nothing OrElse TypeOf W2i Is DBNull Then
                            'If not found add it
                            With cmdAddW2.Parameters
                                .Item("@Native").Value = W2.Native
                                .Item("@Latin").Value = If(W2.Latin = "", CObj(DBNull.Value), W2.Latin)
                                If W2.Type <> enmType.NA Then
                                    .Item("@Type").Value = W2.Type
                                Else
                                    .Item("@Type").Value = DBNull.Value
                                End If
                                .Item("@Note").Value = If(W2.Note = "", CObj(DBNull.Value), W2.Note)
                            End With
                            cmdAddW2.ExecuteNonQuery()
                            Words2.Add(cmdIdentity.ExecuteScalar)
                        Else
                            Words2.Add(W2i)
                        End If
                        W2.ID = Words2(Words2.Count - 1)
                    Next W2
                    'Create translations
                    For Each W1 As Long In Words1
                        cmdAddTrnsl.Parameters("@W1").Value = W1
                        cmdTrnslExists.Parameters("@W1").Value = W1
                        For Each W2 As Long In Words2
                            cmdTrnslExists.Parameters("@W2").Value = W2
                            If CInt(cmdTrnslExists.ExecuteScalar) = 0 Then
                                cmdAddTrnsl.Parameters("@W2").Value = W2
                                cmdAddTrnsl.ExecuteNonQuery()
                            End If
                        Next W2
                    Next W1
                    WK.ReportProgress(i / Dic.Pairs.Count * 100)
                Next Pair
                'Create references
                Dim cmdAddRel As New SqlCeCommand("INSERT INTO Relation ([From], [To], Type) VALUES (@From, @To, @Type)", Conn, tx)
                With cmdAddRel.Parameters
                    .Add("@From", SqlDbType.BigInt)
                    .Add("@To", SqlDbType.BigInt)
                    .Add("@Type", SqlDbType.TinyInt)
                End With
                Dim cmdRelExists As New SqlCeCommand("SELECT COUNT(*) FROM Relation WHERE [From]=@From AND [To]=@To", Conn, tx)
                With cmdRelExists.Parameters
                    .Add("@From", SqlDbType.BigInt)
                    .Add("@To", SqlDbType.BigInt)
                End With
                WK.ReportProgress(0, My.Resources.CreatingReferences)
                i = 0 : For Each Pair As Simple.Dictionary.TranslationPair In Dic.Pairs : i += 1
                    If WK.CancellationPending Then Return Results.Canceled
                    For Each W1 As Simple.Dictionary.Word In Pair.Words1
                        cmdAddRel.Parameters("@From").Value = W1.ID
                        cmdRelExists.Parameters("@From").Value = W1.ID
                        For Each r As Simple.Dictionary.Relation In W1.Relations
                            cmdSrchW1.Parameters(0).Value = r.Word
                            Dim W1i As Object = cmdSrchW1.ExecuteScalar
                            If W1i IsNot Nothing AndAlso Not TypeOf W1i Is DBNull Then
                                cmdRelExists.Parameters("@To").Value = W1i
                                If CInt(cmdRelExists.ExecuteScalar) = 0 Then
                                    cmdAddRel.Parameters("@To").Value = W1i
                                    cmdAddRel.Parameters("@Type").Value = r.Rel
                                    cmdAddRel.ExecuteNonQuery()
                                End If
                            End If
                        Next r
                    Next W1
                    For Each W2 As Simple.Dictionary.Word In Pair.Words2
                        cmdAddRel.Parameters("@From").Value = W2.ID
                        cmdRelExists.Parameters("@From").Value = W2.ID
                        For Each r As Simple.Dictionary.Relation In W2.Relations
                            cmdSrchW2.Parameters(0).Value = r.Word
                            Dim W2i As Object = cmdSrchW2.ExecuteScalar
                            If W2i IsNot Nothing AndAlso Not TypeOf W2i Is DBNull Then
                                cmdRelExists.Parameters("@To").Value = W2i
                                If CInt(cmdRelExists.ExecuteScalar) = 0 Then
                                    cmdAddRel.Parameters("@To").Value = W2i
                                    cmdAddRel.Parameters("@Type").Value = r.Rel
                                    cmdAddRel.ExecuteNonQuery()
                                End If
                            End If
                        Next r
                    Next W2
                    WK.ReportProgress(i / Dic.Pairs.Count * 100)
                Next Pair
                'Copy icons
                WK.ReportProgress(100, My.Resources.Finishing)
                Dim DicIco As Tools.IOt.Path = Dic.Path - 1 + Dic.ICO
                Dim DicIcoTarget As Tools.IOt.Path = IconFolder + Dic.ICO
                If DicIco.IsFile Then
                    Dim Copy As Boolean = True
                    If DicIcoTarget.IsFile Then
                        Copy = ModalMsgBox(String.Format(My.Resources.ThisDictionaryContainsIconButIconWithSameNameAlreadyExistsWithinYourIconDirectory, Dic.ICO), MsgBoxStyle.Question Or MsgBoxStyle.YesNo, My.Resources.OverwriteIcon) = MsgBoxResult.Yes
                    End If
                    If Copy Then
                        Try
                            DicIco.Copy(DicIcoTarget)
                        Catch ex As Exception
                            ModalMsgBox(My.Resources.CopyingOfDictionaryIconFailed & vbCrLf & ex.Message, MsgBoxStyle.Exclamation, My.Resources.Error_)
                        End Try
                    End If
                End If
                If Dic.Primary IsNot Nothing Then
                    Dim ICOSrcs As Tools.IOt.Path() = {Dic.Path - 1 + Dic.Primary.ICO1, Dic.Path - 1 + Dic.Primary.ICO2}
                    Dim ICOTrgs As Tools.IOt.Path() = {IconFolder + Dic.Primary.ICO1, IconFolder + Dic.Primary.ICO2}
                    For ICOi As Integer = 0 To ICOSrcs.Length - 1
                        If ICOSrcs(ICOi).IsFile Then
                            Dim copy As Boolean = True
                            If ICOTrgs(ICOi).IsFile Then
                                copy = ModalMsgBox(String.Format(My.Resources.ThisDictionaryContainsLanguageIconButIconWithSameNameAlreadyExists, ICOSrcs(ICOi).FileName), MsgBoxStyle.Question Or MsgBoxStyle.YesNo, My.Resources.OverwriteIcon) = MsgBoxResult.Yes
                            End If
                            If copy Then
                                Try
                                    ICOSrcs(ICOi).Copy(ICOTrgs(ICOi))
                                Catch ex As Exception
                                    ModalMsgBox(My.Resources.CopyingOfLanguageIconFailed & vbCrLf & ex.Message, MsgBoxStyle.Critical, My.Resources.Error_)
                                End Try
                            End If
                        End If
                    Next ICOi
                End If

                Commit = True
            Finally
                If Commit Then
                    tx.Commit()
                Else
                    tx.Rollback()
                End If
                Conn.Close()
            End Try

        End Function

        Private Sub bgwImport_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgwImport.DoWork
            Try
                e.Result = ImportDictionary(e.Argument, sender)
            Catch ex As Exception
                e.Result = ex
            End Try
        End Sub

        Private Sub bgwImport_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgwImport.ProgressChanged
            pgbStep3.Value = e.ProgressPercentage
            If e.UserState IsNot Nothing Then
                lblStep3Progress.Text = e.UserState
            End If
        End Sub

        Private Sub bgwImport_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwImport.RunWorkerCompleted
            If TypeOf e.Result Is Exception Then
                MsgBox(My.Resources.ErrorDuringImport & vbCrLf & DirectCast(e.Result, Exception).Message, MsgBoxStyle.Critical, My.Resources.Error_)
            Else
                Select Case DirectCast(e.Result, Results)
                    Case Results.Aborted
                        Me.DialogResult = Windows.Forms.DialogResult.Abort
                        CurrentStep = 255
                        Me.Close()
                        Exit Sub
                    Case Results.Canceled
                        Me.DialogResult = Windows.Forms.DialogResult.Cancel
                        CurrentStep = 255
                        Me.Close()
                        Exit Sub
                End Select
            End If
            'GoTo Step 4
            lblStep3Status.ForeColor = Color.Green
            lblStep3Status.Text = My.Resources.MarkDone
            lblStep4Status.Text = My.Resources.MarkDone
            lblStep4Status.ForeColor = Color.Green
            EnabledStep(3) = False
            EnabledStep(4) = True
            CurrentStep = 4
        End Sub

        Private Sub cmdStep3Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStep3Cancel.Click
            bgwImport.CancelAsync()
        End Sub
#End Region
        Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
            Me.Close()
        End Sub
    End Class
End Namespace